<?php
jimport('joomla.application.component.model');
class svModelCanbo extends JModel
{
	//------------ tim kiem----------------------------------
	function getAllCanbo()
	{
		$clause=" SELECT  cb.*, qlc.*, cv.cv_ten,pb.pb_ten,cd.cd_ten,cd_id 
			    FROM    qlcb_canbo as cb,qlcb_quanlychung as qlc,qlcb_phongban as pb,qlcb_chucvu as cv, qlcb_chucdanh as cd
			    WHERE   cb.cb_id = qlc.qlc_idcanbo  
				AND		qlc.qlc_idphongban=pb.pb_id 
				AND		qlc.qlc_idchucvu=cv.cv_id 
				AND		qlc.qlc_idchucdanh=cd.cd_id  ";
		$donvi=JRequest::getInt('iddonvi');
		if($donvi!=0) $clause=$clause.'  AND pb.pb_iddonviquanly='.$donvi.' ';
		$phongban=JRequest::getInt('idphongban');
		if($phongban!=0) $clause=$clause.' AND qlc.qlc_idphongban='.$phongban.' ';
		$canbo=JRequest::getInt('cb_id');
		if($canbo!=0) $clause=$clause.'  AND cb.cb_id='.$canbo.' ';
		$query=$clause.' ORDER BY cb.cb_id DESC ';
		$result=$this->_loadObjectList($query);
		return $result;
	}
	function getTotal()
	{
		$clause=" SELECT COUNT(cb_id) 
			    FROM    qlcb_canbo as cb,qlcb_quanlychung as qlc,qlcb_phongban as pb,qlcb_chucvu as cv, qlcb_chucdanh as cd
			    WHERE   cb.cb_id = qlc.qlc_idcanbo  
				AND		qlc.qlc_idphongban=pb.pb_id 
				AND		qlc.qlc_idchucvu=cv.cv_id 
				AND		qlc.qlc_idchucdanh=cd.cd_id  ";
		$donvi=JRequest::getInt('iddonvi');
		if($donvi!=0) $clause=$clause.'  AND pb.pb_iddonviquanly='.$donvi.' ';
		$phongban=JRequest::getInt('idphongban');
		if($phongban!=0) $clause=$clause.' AND qlc.qlc_idphongban='.$phongban.' ';
		$canbo=JRequest::getInt('cb_id');
		if($canbo!=0) $clause=$clause.'  AND cb.cb_id='.$canbo.' ';
		$result=$this->_loadResult($clause);
		return $result;
	}

	//--------------- select list -----------
	function Listcanbo()
	{
		$where="";
		$donvi=JRequest::getInt('iddonvi');
		if($donvi!=0) $where=',qlcb_quanlychung as qlc,qlcb_phongban as pb
		WHERE qlc.qlc_idcanbo = cb.cb_id
		AND  qlc.qlc_idphongban = pb.pb_id
		AND pb.pb_iddonviquanly='.$donvi;
		$phongban=JRequest::getInt('idphongban');
		if($phongban!=0) $where=',qlcb_quanlychung as qlc WHERE qlc.qlc_idcanbo=cb.cb_id AND qlc.qlc_idphongban='.$phongban;
		$query='SELECT DISTINCT cb.cb_id as `key`,cb.cb_ten as `text`  FROM qlcb_canbo as cb '.$where;
		return $this->_getList($query);
	}
	function ListPhongban()
	{
		$where="";
		$donvi=JRequest::getInt('iddonvi');
		if($donvi!=0) $where=' WHERE pb_iddonviquanly='.$donvi;
		$query='SELECT DISTINCT pb_id as `key`,pb_ten as `text`  FROM qlcb_phongban '.$where;
		return $this->_getList($query);
	}
	function ListDonvi()
	{
		$query='SELECT DISTINCT dvql_id as `key`,dvql_ten as `text`  FROM qlcb_donviquanly';
		return $this->_getList($query);
	}
	function ListChucdanh()
	{
		$query='SELECT DISTINCT cd_id ,cd_ten  FROM qlcb_chucdanh';
		return $this->_getList($query);
	}
	//------------------------------
	function getCanboByID($idcanbo)
	{
		$query='SELECT  cb.*, qlc.*, cv.cv_ten as chucvu,pb.pb_ten as phongban,cd.cd_ten as chucdanh
				FROM    qlcb_canbo as cb,qlcb_quanlychung as qlc,qlcb_phongban as pb,qlcb_chucvu as cv, qlcb_chucdanh as cd
				WHERE   cb.cb_id = qlc.qlc_idcanbo
				AND		qlc.qlc_idphongban=pb.pb_id
				AND		qlc.qlc_idchucvu=cv.cv_id
				AND		qlc.qlc_idchucdanh=cd.cd_id  
				AND 	cb.cb_id='.$idcanbo;
		$result=$this->_loadObject($query);
		return $result;
	}
	function getCanboByIDs($idcanbo)
	{
		$query='SELECT  cb.cb_id,cb.cb_ten,cb.cb_namsinh,cb.cb_hinhanh,cb.cb_diachi,
		cv.cv_ten as chucvu,pb.pb_ten as phongban,cd.cd_ten as chucdanh
		FROM    qlcb_canbo as cb,qlcb_quanlychung as qlc,qlcb_phongban as pb,qlcb_chucvu as cv, qlcb_chucdanh as cd
		WHERE   cb.cb_id = qlc.qlc_idcanbo
		AND		qlc.qlc_idphongban=pb.pb_id
		AND		qlc.qlc_idchucvu=cv.cv_id
		AND		qlc.qlc_idchucdanh=cd.cd_id
		AND 	cb.cb_id='.$idcanbo;
		$result=$this->_loadObjectList($query);
		return $result;
	}
	function getAllPhongban()
	{
		$query='SELECT pb_id,pb_ten
				FROM qlcb_phongban
				WHERE 1';
		$result=$this->_loadObjectList($query);
		return $result;
	}
	function getAllChucvu()
	{
		$query='SELECT cv_id,cv_ten
		FROM qlcb_chucvu
		WHERE 1';
		$result=$this->_loadObjectList($query);
		return $result;
	}
	function getAllChucdanh()
	{
		$query='SELECT cd_id,cd_ten
		FROM qlcb_chucdanh
		WHERE 1';
		$result=$this->_loadObjectList($query);
		return $result;
	}
	function getSunghiepByID($idcanbo)
	{
		$query='SELECT  cb.cb_ten,cb.cb_id,lsn.lsn_id,lsn.lsn_ten,sn.*
		FROM    qlcb_canbo as cb,qlcb_loaisunghiep as lsn, qlcb_sunghiep as sn
		WHERE  	lsn.lsn_id=sn.sn_idloaisunghiep
		AND 	sn.sn_idcanbo=cb.cb_id
		AND 	cb.cb_id='.$idcanbo.' ORDER BY sn.timeupdate DESC';
		$result=$this->_loadObjectList($query);
		return $result;
	}
	function getMaxIDCanbo()
	{
		$query='SELECT MAX(cb_id) as max
		FROM qlcb_canbo
		WHERE 1';
		$result=$this->_loadObject($query);
		return $result->max;
	}
	function delChucdanh($idchucvu)
	{
		$query="DELETE FROM qlcb_chucdanh WHERE cd_id=".$idchucvu;
		$message='Xóa 1 item ';
		$result=$this->_Execute($query, $message);
		return $result;	
	}
	// cac ham phuc vu truy van CSDL
	function _loadResult($query)
	{
		$db= JFactory::getDBO();
		$db->setQuery($query);
		$result=$db->loadResult();
		return $result;
	}
	function _loadObject($query)
	{
		$db= JFactory::getDBO();
		$db->setQuery($query);
		$result=$db->loadObject();
		return $result;
	}
	function _loadObjectList($query)
	{
		$db= JFactory::getDBO();
		$db->setQuery($query);
		$result=$db->loadObjectList();
		return $result;
	}
	function _Execute($query,$message)
	{
		$db= JFactory::getDBO();
		$db->setQuery($query);
		try {
			$db->query();
			return $message." thành công ";
		}
		catch (Exception $e){
			return $message." Không thành công! Vui lòng thử lại.";
		}
	}
}
?>